﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using CrystalDecisions.ReportSource;
using C1.Win.C1FlexGrid;
using BHXH_NT.Properties;
using BHXH_NT.Data;


namespace BHXH_NT
{
    public partial class frmBHYT : Form
    {
        private string connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
        private string connectionString = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString1;
        private string Condition;
        private string strSearch = "", strSearch1 = "";
        Random rnd = new Random();
        public frmBHYT()
        {
            InitializeComponent();
        }
        #region "Method Private"
        //
        //lay danh sach cac don vi in the trong ngay
        private void GetFlexGeneral()
        {

            string queryString;

            if (CboConection.SelectedIndex == 0) //thu
            {
                queryString = "exec sp_executesql N'SELECT Inthe_hdr.KEYSL, Inthe_hdr.KEYGH, Inthe_hdr.MADVI,c.TENDVI, Inthe_hdr.NGAY,"
                + "Inthe_hdr.LOAI, Inthe_hdr.TUNGAY, Inthe_hdr.DENNGAY, Inthe_hdr.MA_QUAN,  Inthe_hdr.USERCODE,Inthe_hdr.GHICHU FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi WHERE NGAY = @P1 AND Inthe_hdr.MADVI NOT LIKE @P2 AND Inthe_hdr.MADVI NOT LIKE @P3 " + strSearch + " Order by c.DSNV,Inthe_hdr.MADVI', N'@P1 smalldatetime,@P2 varchar(4),@P3 varchar(4)', '" + dtNgay.Value.Year + "/" + dtNgay.Value.Month + "/" + dtNgay.Value.Day + "','%BD%','%BE%'";
            }
            else if (CboConection.SelectedIndex == 1) //the
            {
                queryString = "exec sp_executesql N'SELECT Inthe_hdr.KEYSL, Inthe_hdr.KEYGH, Inthe_hdr.MADVI,c.TENDVI, Inthe_hdr.NGAY,"
                + "Inthe_hdr.LOAI, Inthe_hdr.TUNGAY, Inthe_hdr.DENNGAY, Inthe_hdr.MA_QUAN,  Inthe_hdr.USERCODE,Inthe_hdr.GHICHU FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi WHERE NGAY = @P1 AND Inthe_hdr.MADVI NOT LIKE @P2 AND Inthe_hdr.MADVI LIKE @P3 " + strSearch + " Order by c.DSNV,Inthe_hdr.MADVI', N'@P1 smalldatetime,@P2 varchar(4),@P3 varchar(4)', '" + dtNgay.Value.Year + "/" + dtNgay.Value.Month + "/" + dtNgay.Value.Day + "','%BE%','%BD%'";
            }
            else //tre em
            {
                queryString = "exec sp_executesql N'SELECT Inthe_hdr.KEYSL, Inthe_hdr.KEYGH, Inthe_hdr.MADVI,c.TENDVI, Inthe_hdr.NGAY,"
                + "Inthe_hdr.LOAI, Inthe_hdr.TUNGAY, Inthe_hdr.DENNGAY, Inthe_hdr.MA_QUAN,  Inthe_hdr.USERCODE,Inthe_hdr.GHICHU FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi WHERE NGAY = @P1 AND Inthe_hdr.MADVI LIKE @P2 " + strSearch + " Order by c.DSNV,Inthe_hdr.MADVI', N'@P1 smalldatetime,@P2 varchar(4)', '" + dtNgay.Value.Year + "/" + dtNgay.Value.Month + "/" + dtNgay.Value.Day + "','%BE%'";
            }

            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxGeneral.Rows.Count = 1;

                while (reader.Read())
                {
                    FlxGeneral.Rows.Count = FlxGeneral.Rows.Count + 1;
                    FlxGeneral.Rows[i]["STT"] = i;
                    FlxGeneral.Rows[i]["Check"] = 1;
                    FlxGeneral.Rows[i]["MADVI"] = reader["MADVI"];
                    FlxGeneral.Rows[i]["TENDVI"] = reader["TENDVI"];
                    FlxGeneral.Rows[i]["KEYSL"] = reader["KEYSL"];
                    Condition = Condition + "'" + reader["KEYSL"] + "',";
                    i++;
                }
                reader.Close();
                if (Condition != null) { Condition = Condition.Substring(0, Condition.Length - 1); };
            }
        }
        //
        //get nhung nhan vien da tao the nhung chua in the
        private void GetFlexDetail()
        {
            FlxDetail.Rows.Count = 1;
            if (Condition == null) { return; }

            string queryString = "SELECT 0 As Chon, a.STT, a.Madvi,c.TenDVI,c.MADVIKCB,c.MaKhoiKCB as Makhoi,e.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email,"
            + " a.Sobhxh, a.SoKCB, b.Ho, b.Ten, a.TuNgay, a.DenNgay, b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, "
            + " b.Namsinh, CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi,  "
            + " case when a.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else a.Ma_Tinh end as Ma_Tinh, a.Ma_BV,"
            + " case when d.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = a.Ma_BV) else d.Ten_BV end as Ten_BV,"
            + " a.Tinh_Den, a.NgayMua, a.NgayGiao, a.NgayCap, "
            + " a.LanCap, a.MaDT, a.NgayGiam, a.GhiChu, a.KeySL,a.KeyP, a.KeyGH, a.Dain, a.MaPB, "
            + " a.UserCode, a.Ngayin,c.DSNV  FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a "
            + " LEFT JOIN HOSOCN b ON a.Sobhxh=b.Sobhxh  "
            + " LEFT JOIN DMDONVI c ON a.Madvi=c.Madvi "
            + " LEFT JOIN DMBV d on d.Ma_Tinh = a.Ma_Tinh and d.Ma_BV = A.Ma_BV   "
            + " LEFT JOIN DMKHOIKCB e on c.MaKhoiKCB = e.MA_DT "
            + " WHERE a.KeySL IN (" + Condition + ") AND A.USERCODE = '" + ModuleBHXH.strUser + "' and ngayin is null AND (a.Hethan = 0 OR Ngaygiam IS NOT NULL) " + strSearch1 + " Order By c.DSNV,a.madvi, a.Sobhxh ";

            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxDetail.Tree.Column = 4;
                while (reader.Read())
                {
                    if (FlxDetail.Rows[i - 1]["Madvi"].ToString() != reader["Madvi"].ToString()) //insert node moi
                    {
                        FlxDetail.Rows.InsertNode(i, 1);
                    }
                    else
                    {
                        FlxDetail.Rows.InsertNode(i, 2);
                    }
                    // FlxDetail.Rows.Count = FlxDetail.Rows.Count + 1;
                    FlxDetail.SetCellCheck(i, 4, CheckEnum.Checked);

                    FlxDetail.Rows[i]["STT1"] = i;
                    FlxDetail.Rows[i]["Check"] = 1;
                    FlxDetail.Rows[i]["STT"] = reader["STT"];
                    FlxDetail.Rows[i]["Madvi"] = reader["Madvi"];
                    FlxDetail.Rows[i]["Sobhxh"] = reader["Sobhxh"];
                    FlxDetail.Rows[i]["SoKCB"] = reader["SoKCB"];
                    FlxDetail.Rows[i]["Ho"] = reader["Ho"];
                    FlxDetail.Rows[i]["Ten"] = reader["Ten"];
                    FlxDetail.Rows[i]["TuNgay"] = reader["TuNgay"];
                    FlxDetail.Rows[i]["DenNgay"] = reader["DenNgay"];
                    FlxDetail.Rows[i]["NgaySinh"] = reader["NgaySinh"];
                    FlxDetail.Rows[i]["NamSinh"] = reader["NamSinh"];
                    FlxDetail.Rows[i]["GioiTinh"] = reader["GioiTinh"];
                    FlxDetail.Rows[i]["DiaChi"] = ModuleBHXH.ChangeToCamel(reader["DiaChi"].ToString());
                    FlxDetail.Rows[i]["Ten_BV"] = reader["Ten_BV"];
                    FlxDetail.Rows[i]["Ma_BV"] = reader["Ma_BV"];
                    FlxDetail.Rows[i]["Ma_Tinh"] = reader["Ma_Tinh"];
                    FlxDetail.Rows[i]["NgayCap"] = reader["NgayCap"];

                    FlxDetail.Rows[i]["TenDVI"] = reader["TenDVI"];
                    FlxDetail.Rows[i]["Makhoi"] = reader["Makhoi"];
                    FlxDetail.Rows[i]["Ten_DT"] = reader["Ten_DT"];
                    FlxDetail.Rows[i]["DCCty"] = reader["DCCty"];
                    FlxDetail.Rows[i]["dienthoai"] = reader["dienthoai"];
                    FlxDetail.Rows[i]["fax"] = reader["fax"];
                    FlxDetail.Rows[i]["email"] = reader["email"];
                    FlxDetail.Rows[i]["MADVIKCB"] = reader["MADVIKCB"];
                    FlxDetail.Rows[i]["MaPB"] = reader["MaPB"];
                    FlxDetail.Rows[i]["KEYSL"] = reader["KEYSL"];
                    FlxDetail.Rows[i]["KEYP"] = reader["KEYP"];
                    FlxDetail.Rows[i]["DSNV"] = reader["DSNV"];
                    i++;
                }
                reader.Close();
                
            }
        }
        //
        //cap lai 
        private void GetFlexGeneral_Caplai()
        {

            string queryString;
            string strCondition = " and DSNV in (" + Settings.Default.UserThe + ")";
            strCondition = strCondition.Replace("'", "''");

            if (CboConection.SelectedIndex == 0)// thu
            {
                queryString = "exec sp_executesql N'SELECT distinct Inthe_hdr.MADVI,c.TENDVI FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi," + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI d  WHERE d.Madvi = Inthe_hdr.Madvi AND Inthe_hdr.MADVI not LIKE ''%BE%'' " + strSearch + strCondition + "  Order by Inthe_hdr.MADVI'";
            }
            else if (CboConection.SelectedIndex == 1)//the
            {
                queryString = "exec sp_executesql N'SELECT distinct Inthe_hdr.MADVI,c.TENDVI FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi," + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI d  WHERE d.Madvi = Inthe_hdr.Madvi  AND Inthe_hdr.MADVI LIKE @P2 AND Inthe_hdr.MADVI NOT LIKE @P3 " + strSearch + strCondition + "  Order by Inthe_hdr.MADVI', N'@P2 varchar(5),@P3 varchar(5)', '%BD%','%BE%'";
            }
            else // tre em
            {
                queryString = "exec sp_executesql N'SELECT distinct Inthe_hdr.MADVI,c.TENDVI FROM " + Settings.Default.DBName_Sothe + ".dbo.INTHE_HDR Inthe_hdr LEFT JOIN DMDONVI c ON "
                + "Inthe_hdr.Madvi=c.Madvi," + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI d  WHERE d.Madvi = Inthe_hdr.Madvi  AND Inthe_hdr.MADVI LIKE @P2 " + strSearch + strCondition + "  Order by Inthe_hdr.MADVI', N'@P2 varchar(5)', '%BE%'";
            }

            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                FlxGeneral.Rows.Count = 1;

                while (reader.Read())
                {
                    FlxGeneral.Rows.Count = FlxGeneral.Rows.Count + 1;
                    FlxGeneral.Rows[i]["STT"] = i;
                    FlxGeneral.Rows[i]["Check"] = 1;
                    FlxGeneral.Rows[i]["MADVI"] = reader["MADVI"];
                    FlxGeneral.Rows[i]["TENDVI"] = reader["TENDVI"];
                    i++;
                }
                reader.Close();
                if (Condition != null) { Condition = Condition.Substring(0, Condition.Length - 1); };
            }
        }
        //
        //caplai
        private void GetFlexDetail_Caplai()
        {
            FlxDetail.Rows.Count = 1;
            string queryString;
            string strCondition = " and DSNV in (" + Settings.Default.UserThe + ")";
            if (CboConection.SelectedIndex == 0)//thu
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.SoBHXH = a.SoBHXH and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  KeyGH = (select KEYGH from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where KEYSL = (select max(KEYSL) from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where Madvi = a.Madvi and loai = 1)) "
                + " AND a.Madvi not Like '%BE%'  AND a.HetHan = 0 " + strSearch1 + strCondition + " order by c.DSNV,a.Madvi";
            }
            else if (CboConection.SelectedIndex == 1)//the
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.SoBHXH = a.SoBHXH and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  KeyGH = (select KEYGH from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where KEYSL = (select max(KEYSL) from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where Madvi = a.Madvi)) "
                + " AND a.HetHan = 0 AND a.Madvi Like '%BD%' " + strSearch1 + strCondition + " order by c.DSNV,a.Madvi";
            }
            else if (CboConection.SelectedIndex == 2)//tre em
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN " + Settings.Default.DBName + ".Dbo.HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.Sokcb = a.Sokcb and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  a.HetHan = 0 AND left(a.Madvi,2) = 'BE' " + strSearch1 + strCondition + " order by c.DSNV,a.Madvi";
            }
            else if (CboConection.SelectedIndex == 3)//inthe TUNGUYEN 2012
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.SoBHXH = a.SoBHXH and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  KeyGH = (select KEYGH from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where Madvi = a.Madvi and YEAR(NGAY) = '2012' and loai = 1 and left(madvi,2) = 'BI') "
                + " AND a.Madvi not Like '%BE%'  AND a.HetHan = 0 " + strSearch1 + strCondition + " order by c.DSNV,a.Madvi";
            }
            else if (CboConection.SelectedIndex == 4)//hong mat thu
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.SoBHXH = a.SoBHXH and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  KeyGH = (select KEYGH from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where KEYSL = (select max(KEYSL) from " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_HDR where Madvi = a.Madvi and loai = 1)) "
                + " AND a.Madvi not Like '%BE%'  AND a.HetHan = 0 and e.macl in ('01','02') " + strSearch1 + " order by c.DSNV,a.Madvi";
            }
            else
            {
                queryString = "select 0 As Chon,a.STT, a.Madvi,c.TenDVI,c.MaKhoiKCB as Makhoi,c.MADVIKCB,g.Ten_DT,c.Diachi as DCCty,c.dienthoai,c.fax,c.email, a.KeySL,a.KeyP, a.KeyGH, a.Loai,  a.Sobhxh, e.Tencl,"
                + " a.SoKCB, a.TuNgay, a.DenNgay, e.MaCL, a.NgayGiao, a.NgayMua, a.NgayCap, a.LanCap, b.Ho, b.Ten, a.MaTG,"
                + " b.NgaySinh,b.NAMSINH, CASE WHEN b.GioiTinh = 0 THEN 'N÷' else 'Nam' end as GioiTinh, b.Namsinh, a.Madt, case when b.Ma_Tinh = '' then '" + Properties.Settings.Default.MaTinh + "' else b.Ma_Tinh end as Ma_Tinh , b.Ma_BV, a.Tinh_Den, "
                + " case when f.Ten_BV is null then (select Ten_BV from DMBV as bv where bv.Ma_Tinh = '" + Properties.Settings.Default.MaTinh + "' and bv.Ma_BV = b.Ma_BV) else f.Ten_BV end as Ten_BV,"
                + " CASE WHEN c.InTenDvi<>0 THEN c.Tentat ELSE b.DiaChi END As DiaChi, 0 AS NhanGT, b.MaPB,  a.UserCode, "
                + " a.Ngayin, SPACE(50) As Ghichus,a.HSL, a.ML,c.DSNV FROM " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_DTL a LEFT JOIN " + Settings.Default.DBName + ".Dbo.HOSOCN b "
                + " ON a.Sobhxh = b.Sobhxh LEFT JOIN DMDONVI c ON a.Madvi = c.Madvi "
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".Dbo.INTHE_CAPLAI d ON d.Sokcb = a.Sokcb and a.Madvi = d.Madvi"
                + " INNER JOIN " + Settings.Default.DBName_Sothe + ".dbo.LYDOCL e on e.MACL = d.MACL "
                + " LEFT JOIN DMBV f on f.Ma_Tinh = b.Ma_Tinh and f.Ma_BV = b.Ma_BV   "
                + " LEFT JOIN DMKHOIKCB g on c.MaKhoiKCB = g.MA_DT "
                + " where  a.HetHan = 0 AND left(a.Madvi,2) = 'BE' and e.macl in ('01','02') " + strSearch1 + " order by c.DSNV,a.Madvi";
            }
            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;


                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                int i = 1;
                int j = 1;
                FlxDetail.Tree.Column = 4;
                while (reader.Read())
                {
                    if (FlxDetail.Rows[i - 1]["Madvi"].ToString() != reader["Madvi"].ToString())
                    {
                        FlxDetail.Rows.InsertNode(i, 1);
                        j = 1;
                    }
                    else
                    {
                        FlxDetail.Rows.InsertNode(i, 2);
                    }
                    // FlxDetail.Rows.Count = FlxDetail.Rows.Count + 1;
                    FlxDetail.SetCellCheck(i, 4, CheckEnum.Checked);

                    FlxDetail.Rows[i]["STT1"] = i;
                    FlxDetail.Rows[i]["Check"] = 1;
                    FlxDetail.Rows[i]["STT"] = reader["STT"];
                    FlxDetail.Rows[i]["Madvi"] = reader["Madvi"];
                    FlxDetail.Rows[i]["Sobhxh"] = reader["Sobhxh"];
                    FlxDetail.Rows[i]["SoKCB"] = reader["SoKCB"];
                    FlxDetail.Rows[i]["Ho"] = reader["Ho"];
                    FlxDetail.Rows[i]["Ten"] = reader["Ten"];
                    FlxDetail.Rows[i]["TuNgay"] = reader["TuNgay"];
                    FlxDetail.Rows[i]["DenNgay"] = reader["DenNgay"];
                    FlxDetail.Rows[i]["NgaySinh"] = reader["NgaySinh"];
                    FlxDetail.Rows[i]["NamSinh"] = reader["NamSinh"];
                    FlxDetail.Rows[i]["GioiTinh"] = reader["GioiTinh"];
                    FlxDetail.Rows[i]["DiaChi"] = reader["DiaChi"];
                    FlxDetail.Rows[i]["Ma_BV"] = reader["Ma_BV"];
                    FlxDetail.Rows[i]["Ma_Tinh"] = reader["Ma_Tinh"];
                    FlxDetail.Rows[i]["NgayCap"] = reader["NgayCap"];
                    FlxDetail.Rows[i]["Ten_BV"] = reader["Ten_BV"];
                    FlxDetail.Rows[i]["TenDVI"] = reader["TenDVI"];
                    FlxDetail.Rows[i]["Makhoi"] = reader["Makhoi"];
                    FlxDetail.Rows[i]["KEYP"] = reader["KEYP"];
                    FlxDetail.Rows[i]["KEYSL"] = reader["KEYSL"];
                    FlxDetail.Rows[i]["KEYGH"] = reader["KEYGH"];
                    FlxDetail.Rows[i]["MADT"] = reader["MADT"];
                    FlxDetail.Rows[i]["MATG"] = reader["MATG"];
                    FlxDetail.Rows[i]["loai"] = reader["loai"];
                    FlxDetail.Rows[i]["MACL"] = reader["MACL"];
                    FlxDetail.Rows[i]["LanCap"] = reader["LanCap"]; //SO LAN CAP LAI
                    FlxDetail.Rows[i]["Tencl"] = reader["Tencl"]; //TÊN CAP LAI


                    FlxDetail.Rows[i]["Ten_DT"] = reader["Ten_DT"];
                    FlxDetail.Rows[i]["DCCty"] = reader["DCCty"];
                    FlxDetail.Rows[i]["dienthoai"] = reader["dienthoai"];
                    FlxDetail.Rows[i]["fax"] = reader["fax"];
                    FlxDetail.Rows[i]["email"] = reader["email"];
                    FlxDetail.Rows[i]["MADVIKCB"] = reader["MADVIKCB"];
                    FlxDetail.Rows[i]["MaPB"] = reader["MaPB"];
                    FlxDetail.Rows[i]["HSL"] = reader["HSL"];
                    FlxDetail.Rows[i]["ML"] = reader["ML"];
                    FlxDetail.Rows[i]["DSNV"] = reader["DSNV"];
                    i++;
                    j++;
                }
                reader.Close();
                

            }
        }
        private System.Windows.Forms.DialogResult CheckCapLai()
        {
            for (int i = 1; i < FlxDetail.Rows.Count; i++)
            {
                if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked && ModuleBHXH.FormatDate(Convert.ToDateTime(FlxDetail.Rows[i]["NgayCap"])) != BHXH_NT.ModuleBHXH.strDate)
                {
                    return MessageBox.Show("ngµy cÊp kh¸c ngµy hiÖn t¹i, tiÕp tôc in.", "th«ng b¸o", MessageBoxButtons.OKCancel);
                }
            }
            return System.Windows.Forms.DialogResult.OK;
        }
        #endregion

        #region "Method Event"
        private void frmBHYT_Load(object sender, EventArgs e)
        {

            CboConection.SelectedIndex = 0;
            cboLoaiIn.SelectedIndex = 0;
            cboIn.SelectedIndex = 0;

        }
        /// <summary>
        /// gop hai the thanh mot record
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnIn_Click(object sender, EventArgs e)
        {
            if (FlxDetail.Rows.Count == 1) { return; }
            if (CheckCapLai() == System.Windows.Forms.DialogResult.Cancel) { return; }
            BHYT dsbhyt = new BHYT();
            bool blnFlag = false;
            DataRow newRow = null;
            for (int i = 1; i < FlxDetail.Rows.Count; i++)
            {
                if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                {

                    if (blnFlag == false) 
                    {
                        newRow = dsbhyt.Tables[0].NewRow();
                        newRow["SOKCB1"] = FlxDetail.Rows[i]["SoKCB"];
                        newRow["SOKCB11"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(0, 2);
                        newRow["SOKCB12"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(2, 1);
                        newRow["SOKCB13"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(3, 2);
                        newRow["SOKCB14"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(5, 2);
                        newRow["SOKCB15"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(7, 3);
                        newRow["SOKCB16"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(10);
                        newRow["Ho1"] = FlxDetail.Rows[i]["Ho"];
                        newRow["Ten1"] = FlxDetail.Rows[i]["Ten"];
                        newRow["Hoten1"] = FlxDetail.Rows[i]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i]["Ten"].ToString().Trim();
                        newRow["TuNgay1"] = FlxDetail.Rows[i]["TuNgay"].ToString().Substring(0, 10);
                        newRow["DenNgay1"] = FlxDetail.Rows[i]["DenNgay"].ToString().Substring(0, 10);
                        if (FlxDetail.Rows[i]["NamSinh"] == null  || FlxDetail.Rows[i]["NamSinh"].ToString() == "0" )
                        {
                            newRow["NgaySinh1"] = FlxDetail.Rows[i]["NgaySinh"].ToString().Substring(0, 10);
                        }
                        else
                        {
                            newRow["NgaySinh1"] = FlxDetail.Rows[i]["NgaySinh"].ToString().Substring(6, 4);
                        }
                        newRow["MaPB1"] = FlxDetail.Rows[i]["MaPB"];
                        newRow["GioiTinh1"] = FlxDetail.Rows[i]["GioiTinh"];
                        newRow["DiaChi1"] = FlxDetail.Rows[i]["DiaChi"];
                        newRow["Ten_BV1"] = FlxDetail.Rows[i]["Ten_BV"];
                        newRow["Ma_BV1"] = FlxDetail.Rows[i]["Ma_Tinh"] + " " + FlxDetail.Rows[i]["Ma_BV"];
                        newRow["NgayCap1"] = "Ngµy " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(0, 2) + " th¸ng " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(3, 2) + " n¨m " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(6, 4);
                        blnFlag = true;
                    }
                    else
                    {
                        newRow["SOKCB2"] = FlxDetail.Rows[i]["SoKCB"];
                        newRow["SOKCB21"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(0, 2);
                        newRow["SOKCB22"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(2, 1);
                        newRow["SOKCB23"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(3, 2);
                        newRow["SOKCB24"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(5, 2);
                        newRow["SOKCB25"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(7, 3);
                        newRow["SOKCB26"] = FlxDetail.Rows[i]["SoKCB"].ToString().Substring(10);
                        newRow["Ho2"] = FlxDetail.Rows[i]["Ho"];
                        newRow["Ten2"] = FlxDetail.Rows[i]["Ten"];
                        newRow["Hoten2"] = FlxDetail.Rows[i]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i]["Ten"].ToString().Trim();
                        newRow["TuNgay2"] = FlxDetail.Rows[i]["TuNgay"].ToString().Substring(0, 10);
                        newRow["DenNgay2"] = FlxDetail.Rows[i]["DenNgay"].ToString().Substring(0, 10);
                        if (FlxDetail.Rows[i]["NamSinh"] == null || FlxDetail.Rows[i]["NamSinh"].ToString() == "0" )
                        {
                            newRow["NgaySinh2"] = FlxDetail.Rows[i]["NgaySinh"].ToString().Substring(0, 10);
                        }
                        else
                        {
                            newRow["NgaySinh2"] = FlxDetail.Rows[i]["NgaySinh"].ToString().Substring(6, 4);
                        }
                        newRow["MaPB2"] = FlxDetail.Rows[i]["MaPB"];
                        newRow["GioiTinh2"] = FlxDetail.Rows[i]["GioiTinh"];
                        newRow["DiaChi2"] = FlxDetail.Rows[i]["DiaChi"];
                        newRow["Ten_BV2"] = FlxDetail.Rows[i]["Ten_BV"];
                        newRow["Ma_BV2"] = FlxDetail.Rows[i]["Ma_Tinh"] + " " + FlxDetail.Rows[i]["Ma_BV"];
                        newRow["NgayCap2"] = "Ngµy " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(0, 2) + " th¸ng " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(3, 2) + " n¨m " + FlxDetail.Rows[i]["NgayCap"].ToString().Substring(6, 4);
                        blnFlag = false;
                    }
                    if (blnFlag == false) { dsbhyt.Tables[0].Rows.Add(newRow); };
                }

            }
            if (blnFlag == true) { dsbhyt.Tables[0].Rows.Add(newRow); };
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            if (CboConection.SelectedIndex == 2 || CboConection.SelectedIndex == 5)
            {
                cr.Load("crpBHYTTREEM.rpt");
            }
            else
            {
                switch (cboIn.SelectedIndex)
                {
                    case 0:
                        cr.Load("crpBHYT.rpt");
                        break;
                    case 1:
                        cr.Load("crpBHYT_06.rpt");
                        break;
                    case 2:
                        cr.Load("crpBHYT_04.rpt");
                        break;
                    case 3:
                        cr.Load("crpBHYT_01.rpt");
                        break;


                }
            }

            cr.SetDataSource(dsbhyt);
            f.crpView.ReportSource = cr;
            f.Show();
        }
        //
        //in danh sach
        private void btnInDanhSach_Click(object sender, EventArgs e)
        {
            danhsachthe dsbhyt = new danhsachthe();
            for (int i = 1; i < FlxDetail.Rows.Count; i = i + 1)
            {
                if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                {
                    DataRow newRow = dsbhyt.Tables[0].NewRow();
                    newRow["makhoi"] = FlxDetail.Rows[i]["makhoi"];
                    newRow["Ten_DT"] = FlxDetail.Rows[i]["Ten_DT"];
                    newRow["TenDVI"] = FlxDetail.Rows[i]["TenDVI"];
                    newRow["Madvi"] = FlxDetail.Rows[i]["Madvi"];
                    newRow["DCCty"] = FlxDetail.Rows[i]["DCCty"];
                    newRow["dienthoai"] = FlxDetail.Rows[i]["dienthoai"];
                    newRow["fax"] = FlxDetail.Rows[i]["fax"];
                    newRow["email"] = FlxDetail.Rows[i]["email"];
                    newRow["Sobhxh"] = FlxDetail.Rows[i]["Sobhxh"];
                    newRow["SOKCB"] = FlxDetail.Rows[i]["SoKCB"];
                    newRow["Hoten"] = FlxDetail.Rows[i]["Ho"].ToString().Trim() + " " + FlxDetail.Rows[i]["Ten"].ToString().Trim();
                    newRow["TuNgay"] = FlxDetail.Rows[i]["TuNgay"].ToString().Substring(0, 10);
                    newRow["DenNgay"] = FlxDetail.Rows[i]["DenNgay"].ToString().Substring(0, 10);
                    newRow["NgaySinh"] = FlxDetail.Rows[i]["NgaySinh"];
                    if (FlxDetail.Rows[i]["GioiTinh"].ToString() == "N÷")
                    {
                        newRow["GioiTinh"] = "X";
                    }
                    else
                    {
                        newRow["GioiTinh"] = "";
                    }
                    newRow["DiaChi"] = FlxDetail.Rows[i]["DiaChi"];
                    newRow["Ten_BV"] = FlxDetail.Rows[i]["Ten_BV"];
                    newRow["Ma_BV"] = FlxDetail.Rows[i]["Ma_BV"];

                    dsbhyt.Tables[0].Rows.Add(newRow);
                }

            }

            Report f = new Report();
            ReportDocument cr = new ReportDocument();
           
            if (cboLoaiIn.SelectedIndex == 0)
            {
                cr.Load("crpListBHYT.rpt");
            }
            else
            {
                cr.Load("crpListBHYTCL.rpt");
            }

            cr.SetDataSource(dsbhyt);
            foreach (CrystalDecisions.CrystalReports.Engine.FormulaFieldDefinition thisFormulaField in cr.DataDefinition.FormulaFields)
            {
                switch (thisFormulaField.Name)
                {
                    case "truongphong":
                        thisFormulaField.Text = "' §íi Duy Xu©n '";
                        break;
                    case "nguoilap":
                        thisFormulaField.Text = "'" + global::BHXH_NT.Properties.Settings.Default.Nguoilap + "'";
                        break;

                }
            }
            f.crpView.ReportSource = cr;
            f.Show();
        }
        /// <summary>
        /// hien thi so lieu
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSolieu_Click(object sender, EventArgs e)
        {
            Condition = null;
            if (cboNV.Text != "") // truong hop loc in theo nhan vien
            {
                strSearch1 = " AND DSNV LIKE '%" + cboNV.Text + "%'";
                strSearch = " AND DSNV LIKE ''%" + cboNV.Text + "%''";
            }

            if (cboLoaiIn.SelectedIndex == 0) // truong hop cap moi, gia han
            {
                GetFlexGeneral();
                GetFlexDetail();
            }
            else // truong hop cap lai
            {
                GetFlexGeneral_Caplai();
                GetFlexDetail_Caplai();
            }
            //kiem tra thoi gian truong hop tre em
            CheckThoiGianCapThe();

        }
        /// <summary>
        /// check hay uncheck ca don vi
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FlxDetail_Click(object sender, EventArgs e)
        {
            if (FlxDetail.Selection.r1 < 0) { return; }
            if (FlxDetail.Rows[FlxDetail.Selection.r1 - 1]["Madvi"] == null) { return; }
            if (FlxDetail.Rows[FlxDetail.Selection.r1]["Madvi"].ToString() != FlxDetail.Rows[FlxDetail.Selection.r1 - 1]["Madvi"].ToString() &&
                FlxDetail.GetCellCheck(FlxDetail.Selection.r1, 4) == CheckEnum.Unchecked)
            {
                int i = 1;
                while (FlxDetail.Selection.r1 + i < FlxDetail.Rows.Count && FlxDetail.Rows[FlxDetail.Selection.r1]["Madvi"].ToString() == FlxDetail.Rows[FlxDetail.Selection.r1 + i]["Madvi"].ToString())
                {
                    FlxDetail.SetCellCheck(FlxDetail.Selection.r1 + i, 4, CheckEnum.Unchecked);
                    i++;

                }

            }
            else if (FlxDetail.Rows[FlxDetail.Selection.r1]["Madvi"].ToString() != FlxDetail.Rows[FlxDetail.Selection.r1 - 1]["Madvi"].ToString() &&
                FlxDetail.GetCellCheck(FlxDetail.Selection.r1, 4) == CheckEnum.Checked)
            {
                int i = 1;
                while (FlxDetail.Selection.r1 + i < FlxDetail.Rows.Count && FlxDetail.Rows[FlxDetail.Selection.r1]["Madvi"].ToString() == FlxDetail.Rows[FlxDetail.Selection.r1 + i]["Madvi"].ToString())
                {
                    FlxDetail.SetCellCheck(FlxDetail.Selection.r1 + i, 4, CheckEnum.Checked);
                    i++;

                }

            }
        }
        /// <summary>
        /// In bien bang
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBB_Click(object sender, EventArgs e)
        {
            if (FlxDetail.Rows.Count == 1) { return; }
            bb dsBB = new bb();
            int intStt = 0, i;
            for (i = 1; i < FlxDetail.Rows.Count; i = i + 1)
            {
                if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                {

                    if (i != 1 && FlxDetail.Rows[i - 1]["Madvi"].ToString() != FlxDetail.Rows[i]["Madvi"].ToString())
                    {
                        DataRow newRow = dsBB.Tables[0].NewRow();
                        newRow["makhoi"] = FlxDetail.Rows[i - 1]["makhoi"];
                        newRow["TenDVI"] = FlxDetail.Rows[i - 1]["TenDVI"];
                        newRow["Madvi"] = FlxDetail.Rows[i - 1]["Madvi"];
                        newRow["MADVIKCB"] = FlxDetail.Rows[i - 1]["MADVIKCB"];
                        newRow["TuNgay"] = FlxDetail.Rows[i - 1]["TuNgay"].ToString().Substring(0, 10);
                        newRow["DenNgay"] = FlxDetail.Rows[i - 1]["DenNgay"].ToString().Substring(0, 10);
                        newRow["SoLuong"] = intStt;

                        dsBB.Tables[0].Rows.Add(newRow);
                        intStt = 1;
                    }
                    else
                    {
                        intStt++;
                    }


                }
            }
            if (i == FlxDetail.Rows.Count)
            {
                DataRow newRow = dsBB.Tables[0].NewRow();
                newRow["makhoi"] = FlxDetail.Rows[i - 1]["makhoi"];
                newRow["TenDVI"] = FlxDetail.Rows[i - 1]["TenDVI"];
                newRow["Madvi"] = FlxDetail.Rows[i - 1]["Madvi"];
                newRow["MADVIKCB"] = FlxDetail.Rows[i - 1]["MADVIKCB"];
                newRow["TuNgay"] = FlxDetail.Rows[i - 1]["TuNgay"].ToString().Substring(0, 10);
                newRow["DenNgay"] = FlxDetail.Rows[i - 1]["DenNgay"].ToString().Substring(0, 10);
                newRow["SoLuong"] = intStt;
                dsBB.Tables[0].Rows.Add(newRow);
            }
            Report f = new Report();
            ReportDocument cr = new ReportDocument();
            cr.Load("crpBB.rpt");
            cr.SetDataSource(dsBB);
            f.crpView.ReportSource = cr;
            f.Show();
        }
        /// <summary>
        /// thay doi connectiontring 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void CboConection_SelectedIndexChanged(object sender, EventArgs e)
        {
           
            if (CboConection.SelectedIndex == 0 || CboConection.SelectedIndex == 2)
            {
                connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
            }
            else
            {
                connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString;
                //connectionString1 = global::BHXH_NT.Properties.Settings.Default.MISBHXH_NTConnectionString3;
            }
        }
        /// <summary>
        /// phong to thu nho luoi chi tiet
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPhongTo_Click(object sender, EventArgs e)
        {
            Point p = new Point();
            p.X = 0;
            p.Y = 0;
            if (FlxDetail.Location.X == 10)
            {
                p.X = 350;
                p.Y = FlxDetail.Location.Y;
                FlxDetail.Location = p;
                FlxDetail.Width = FlxDetail.Width - 340;
            }
            else
            {
                p.X = 10;
                p.Y = FlxDetail.Location.Y;
                FlxDetail.Location = p;
                FlxDetail.Width = FlxDetail.Width + 340;
            }
        }
        /// <summary>
        /// update ngay thang in lai, user in cho truong hop cap lai
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnChapNhan_Click(object sender, EventArgs e)
        {
            if (cboLoaiIn.SelectedIndex != 1 || FlxDetail.Rows.Count == 1) { return; }
            string queryString;
            int intCaplai = 0;
            string strMadviTam = "";
            int soluong = 0;
            if (MessageBox.Show("ChÊp nhËn cÊp l¹i thÎ?", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
            {
                return;
            }
            using (SqlConnection connection =
                   new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                for (int i = 1; i < FlxDetail.Rows.Count; i++)
                {
                    if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                    {

                        if (FlxDetail.Rows[i]["MACL"].ToString() == "02")
                        {
                            queryString = "exec sp_executesql N'UPDATE " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL SET DENNGAY=@P1,HETHAN=@P2,THUHOI=@P3,THAYDOI=@P4 WHERE KEYSL=@P5 AND "
                           + " KEYP=@P6', N'@P1 smalldatetime,@P2 smallint,@P3 smallint,@P4 smalldatetime,@P5 char(20),@P6 char(8)', "
                           + " '" + ModuleBHXH.FormatDate(DateTime.Now) + "', 1, 0, '" + ModuleBHXH.FormatDate(DateTime.Now) + "', '" + FlxDetail.Rows[i]["KEYSL"] + "', '" + FlxDetail.Rows[i]["KEYP"] + "'";
                        }
                        else
                        {
                            queryString = "exec sp_executesql N'UPDATE " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL SET DENNGAY=@P1,HETHAN=@P2,THUHOI=@P3,THAYDOI=@P4 WHERE KEYSL=@P5 AND "
                           + " KEYP=@P6', N'@P1 smalldatetime,@P2 smallint,@P3 smallint,@P4 smalldatetime,@P5 char(20),@P6 char(8)', "
                           + " '" + ModuleBHXH.FormatDate(DateTime.Now) + "', 1, 1, '" + ModuleBHXH.FormatDate(DateTime.Now) + "', '" + FlxDetail.Rows[i]["KEYSL"] + "', '" + FlxDetail.Rows[i]["KEYP"] + "'";
                        }
                        command.CommandText = queryString;
                        command.ExecuteNonQuery();
                        intCaplai = Convert.ToInt32(FlxDetail.Rows[i]["lancap"]) + 1;
                        queryString = "exec sp_executesql N'INSERT INTO " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL (KEYSL,KEYP,KEYGH,MADVI,LOAI,STT,SOBHXH,SOKCB,MADT,MATG,NGAYGIAO,NGAYMUA,"
                        + " TUNGAY,DENNGAY,MA_TINH,MA_BV,NGAYCAP,LANCAP,MACL,TINH_DEN,MAPB,THAYDOI,USERCODE,MA_QUAN,DAIN,NGAYIN,USERIN,HSL,ML) "
                        + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23,@P24,@P25,@P26,@P27,@P28,@P29)', "
                        + " N'@P1 char(20),@P2 char(8),@P3 char(8),@P4 char(7),@P5 char(1),@P6 numeric(6,0),@P7 char(16),@P8 char(16),@P9 char(2),"
                        + " @P10 char(2),@P11 smalldatetime,@P12 smalldatetime,@P13 smalldatetime,@P14 smalldatetime,@P15 char(2),@P16 char(3),@P17"
                        + " smalldatetime,@P18 smallint,@P19 char(2),@P20 char(2),@P21 varchar(30),@P22 smalldatetime,@P23 char(10),@P24 char(2),@P25 smallint,@P26 smalldatetime,@P27 varchar(10),@P28 numeric(6,3),@P29 numeric(18,3)', "
                        + " '" + FlxDetail.Rows[i]["KEYSL"] + "', '" + ModuleBHXH.CreateRunninNo1(rnd) + "', '" + FlxDetail.Rows[i]["KEYGH"] + "', '" + FlxDetail.Rows[i]["MADVI"] + "',"
                        + " '" + FlxDetail.Rows[i]["Loai"] + "', " + FlxDetail.Rows[i]["STT"] + ", '" + FlxDetail.Rows[i]["Sobhxh"] + "', '" + FlxDetail.Rows[i]["SoKCB"] + "', '" + FlxDetail.Rows[i]["MADT"] + "', "
                        + " '" + FlxDetail.Rows[i]["MATG"] + "', '" + ModuleBHXH.FormatDate(DateTime.Now) + "', '" + ModuleBHXH.FormatDate(DateTime.Now) + "', '" + ModuleBHXH.FormatDate(Convert.ToDateTime(FlxDetail.Rows[i]["TuNgay"])) + "', '" + ModuleBHXH.FormatDate(Convert.ToDateTime(FlxDetail.Rows[i]["DenNgay"])) + "',"
                        + " '" + FlxDetail.Rows[i]["MA_TINH"] + "', '" + FlxDetail.Rows[i]["MA_BV"] + "', '" + ModuleBHXH.FormatDate(DateTime.Now) + "'," + intCaplai + ","
                        + " '" + FlxDetail.Rows[i]["MACL"] + "', '  ', '" + FlxDetail.Rows[i]["MaPB"]+ "', '" + ModuleBHXH.FormatDate(DateTime.Now) + "', '" + ModuleBHXH.strUser + "', "
                        + " '01',1,'" + ModuleBHXH.FormatDate(DateTime.Now) + "','" + ModuleBHXH.strUser + "'," +  BHXH_NT.ModuleBHXH.FormatNumner(FlxDetail.Rows[i]["HSL"]) + "," + BHXH_NT.ModuleBHXH.FormatNumner(FlxDetail.Rows[i]["ML"]);
                        command.CommandText = queryString;
                        command.ExecuteNonQuery();
                    }

                }
                for (int i = 1; i < FlxDetail.Rows.Count; i++)
                {
                    if (strMadviTam != FlxDetail.Rows[i]["Madvi"].ToString())
                    {
                        if (soluong != 0 && CheckSoLuongThe(soluong) > 0)
                        {
                            queryString = "exec sp_executesql N'INSERT INTO dbo.CTUVT_SD "
                            + " (SOCT,NGAYCT,NX,MAKHO,MAVT,SOLUONG,TUSO,DENSO,NOIDUNG,USERCODE,KEYSL) "
                            + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)', "
                            + " N'@P1 char(10),@P2 smalldatetime,@P3 char(1),@P4 char(10),@P5 char(10),@P6 numeric(18,0),"
                            + " @P7 varchar(15),@P8 varchar(15),@P9 varchar(100),@P10 char(10),@P11 char(20)', "
                            + " '          ', '" + ModuleBHXH.GetDate() + " ', 'X', '" + ModuleBHXH.strUser + "', 'TT        ', " + soluong + ", "
                            + "'               ', '               ', 'CÊp thÎ cho ®¬n vÞ " + strMadviTam + "', "
                            + " '" + ModuleBHXH.strUser + "', '" + ModuleBHXH.CreateRunninNo(rnd) + "'";


                            command.CommandText = queryString;
                            command.ExecuteNonQuery();
                        }
                        strMadviTam = FlxDetail.Rows[i]["Madvi"].ToString();
                        soluong = 1;
                    }
                    else
                    {
                        soluong++;
                    }
                }
                if (soluong != 0 && CheckSoLuongThe(soluong) > 0)
                {
                    queryString = "exec sp_executesql N'INSERT INTO dbo.CTUVT_SD "
                    + " (SOCT,NGAYCT,NX,MAKHO,MAVT,SOLUONG,TUSO,DENSO,NOIDUNG,USERCODE,KEYSL) "
                    + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)', "
                    + " N'@P1 char(10),@P2 smalldatetime,@P3 char(1),@P4 char(10),@P5 char(10),@P6 numeric(18,0),"
                    + " @P7 varchar(15),@P8 varchar(15),@P9 varchar(100),@P10 char(10),@P11 char(20)', "
                    + " '          ', '" + ModuleBHXH.GetDate() + " ', 'X', '" + ModuleBHXH.strUser + "', 'TT        ', " + soluong + ", "
                    + "'               ', '               ', 'CÊp thÎ cho ®¬n vÞ " + strMadviTam + "', "
                    + " '" + ModuleBHXH.strUser + "', '" + ModuleBHXH.CreateRunninNo(rnd) + "'";
                    command.CommandText = queryString;
                    command.ExecuteNonQuery();
                }
                else if (soluong != 0)
                {
                    MessageBox.Show("xuất quá số lượng tờ rời");
                }
            }
            
            MessageBox.Show("CËp nhËt thµnh c«ng");
            btnSolieu_Click(null, null);

        }
        /// <summary>
        /// insert thoi han the doi voi truong hop tre em
        /// </summary>
        private void CheckThoiGianCapThe()
        {
            if (CboConection.SelectedIndex == 2 && cboLoaiIn.SelectedIndex == 1)
            {
                for (int i = 1; i < FlxDetail.Rows.Count; i++)
                {
                    if (FlxDetail.Rows[i]["MACL"].ToString() == "03")
                    {                        
                        FlxDetail.Rows[i]["DENNGAY"] = Convert.ToDateTime(FlxDetail.Rows[i]["NGAYSINH"]).AddYears(6).AddDays(-1);
                    }
                }
            }
        }
        private void txtDelete_Click(object sender, EventArgs e)
        {
            string queryString;
            if (FlxDetail.Selection.TopRow >= 1)
            {
                Condition = "'" + FlxDetail.Rows[FlxDetail.Selection.TopRow]["SOBHXH"] + "'";
            }
            if (Condition == "") { return; }
            if (MessageBox.Show("ChÊp nhËn xãa danh s¸ch cÊp l¹i thÎ", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
            {
                return;
            }
            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                queryString = "delete " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI where SOBHXH = " + Condition ;
                command.CommandText = queryString;
                command.ExecuteNonQuery();

            }
            MessageBox.Show("Xãa danh s¸ch thµnh c«ng");
            btnSolieu_Click(null, null);
        }
        /// <summary>
        /// xoa du lieu tam trong table Inthe_caplai
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnXoa_Click(object sender, EventArgs e)
        {
            string queryString;
            for (int i = 1; i < FlxDetail.Rows.Count; i++)
            {
                if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                {
                    Condition = Condition + "'" + FlxDetail.Rows[i]["SOBHXH"] + "',";
                }
            }
            
            if (Condition == "") { return; }
            Condition = Condition.Substring(0, Condition.Length - 1);
            if (MessageBox.Show("ChÊp nhËn xãa danh s¸ch cÊp l¹i thÎ", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
            {
                return;
            }
            using (SqlConnection connection =
                   new SqlConnection(connectionString1))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                queryString = "delete " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI where SOBHXH  IN (" + Condition + ")";
                command.CommandText = queryString;
                command.ExecuteNonQuery();

            }
            MessageBox.Show("Xãa danh s¸ch thµnh c«ng");
            btnSolieu_Click(null, null);

            //string queryString;
            //for (int i = 1; i < FlxGeneral.Rows.Count; i++)
            //{
            //    if (FlxGeneral.Rows[i]["Check"].ToString() == "True")
            //    {
            //        Condition = Condition + "'" + FlxGeneral.Rows[i]["MADVI"] + "',";
            //    }
            //}
            //if (Condition == "") { return; }
            //if (MessageBox.Show("ChÊp nhËn xãa danh s¸ch cÊp l¹i thÎ", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
            //{
            //    return;
            //}
            //Condition = Condition.Substring(0, Condition.Length - 1);
            //using (SqlConnection connection =
            //       new SqlConnection(connectionString1))
            //{
            //    connection.Open();
            //    SqlCommand command = connection.CreateCommand();
            //    queryString = "delete " + Settings.Default.DBName_Sothe + ".dbo.INTHE_CAPLAI where MADVI IN (" + Condition + ")";
            //    command.CommandText = queryString;
            //    command.ExecuteNonQuery();

            //}
            //MessageBox.Show("Xãa danh s¸ch thµnh c«ng");
            //btnSolieu_Click(null, null);
        }
        /// <summary>
        /// cap nhat ngay in va userin truong hop tang moi, gia han the
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNgayin_Click(object sender, EventArgs e)
        {
            string queryString;
            string strMadviTam = "";
            int soluong = 0;
            if (MessageBox.Show("ChÊp nhËn cËp nhËt ngµy in", "bhxh", MessageBoxButtons.YesNo) == DialogResult.No)
            {
                return;
            }
            if (cboLoaiIn.SelectedIndex == 0)
            {
                using (SqlConnection connection =
                   new SqlConnection(connectionString))
                {
                    SqlCommand command = connection.CreateCommand();
                    connection.Open();
                    for (int i = 1; i < FlxDetail.Rows.Count; i++)
                    {
                        if (FlxDetail.GetCellCheck(i, 4) == CheckEnum.Checked)
                        {
                            queryString = "exec sp_executesql N'UPDATE " + Settings.Default.DBName_Sothe + ".dbo.INTHE_DTL SET DAIN=@P1,NGAYIN=@P2,USERIN=@P3"
                            + " WHERE KEYSL=@P4 AND KEYP=@P5', N'@P1 smallint,@P2 smalldatetime,@P3 varchar(10),@P4 char(20),@P5 char(8)'"
                            + ", 1, '" + ModuleBHXH.GetDate() + "', '" + ModuleBHXH.strUser + "', '" + FlxDetail.Rows[i]["KEYSL"].ToString().Trim() + "', '" + FlxDetail.Rows[i]["KEYP"].ToString().Trim() + "'";
                            command.CommandText = queryString;
                            command.ExecuteNonQuery();
                        }
                    }
                    for (int i = 1; i < FlxDetail.Rows.Count; i++)
                    {
                        if (strMadviTam != FlxDetail.Rows[i]["Madvi"].ToString())
                        {
                            if (soluong != 0 && CheckSoLuongThe(soluong) > 0)
                            {
                                queryString = "exec sp_executesql N'INSERT INTO dbo.CTUVT_SD "
                                + " (SOCT,NGAYCT,NX,MAKHO,MAVT,SOLUONG,TUSO,DENSO,NOIDUNG,USERCODE,KEYSL) "
                                + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)', "
                                + " N'@P1 char(10),@P2 smalldatetime,@P3 char(1),@P4 char(10),@P5 char(10),@P6 numeric(18,0),"
                                + " @P7 varchar(15),@P8 varchar(15),@P9 varchar(100),@P10 char(10),@P11 char(20)', "
                                + " '          ', '" + ModuleBHXH.GetDate() + " ', 'X', '" + ModuleBHXH.strUser + "', 'TT        ', " + soluong + ", "
                                + "'               ', '               ', 'CÊp thÎ cho ®¬n vÞ " + strMadviTam + "', "
                                + " '" + ModuleBHXH.strUser + "', '" + ModuleBHXH.CreateRunninNo(rnd) + "'";


                                command.CommandText = queryString;
                                command.ExecuteNonQuery();
                            }
                            strMadviTam = FlxDetail.Rows[i]["Madvi"].ToString();
                            soluong = 1;
                        }
                        else
                        {
                            soluong++;
                        }
                    }
                    if (soluong != 0 && CheckSoLuongThe(soluong) > 0)
                    {
                        queryString = "exec sp_executesql N'INSERT INTO dbo.CTUVT_SD "
                        + " (SOCT,NGAYCT,NX,MAKHO,MAVT,SOLUONG,TUSO,DENSO,NOIDUNG,USERCODE,KEYSL) "
                        + " VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11)', "
                        + " N'@P1 char(10),@P2 smalldatetime,@P3 char(1),@P4 char(10),@P5 char(10),@P6 numeric(18,0),"
                        + " @P7 varchar(15),@P8 varchar(15),@P9 varchar(100),@P10 char(10),@P11 char(20)', "
                        + " '          ', '" + ModuleBHXH.GetDate() + " ', 'X', '" + ModuleBHXH.strUser + "', 'TT        ', " + soluong + ", "
                        + "'               ', '               ', 'CÊp thÎ cho ®¬n vÞ " + strMadviTam + "', "
                        + " '" + ModuleBHXH.strUser + "', '" + ModuleBHXH.CreateRunninNo(rnd) + "'";
                        command.CommandText = queryString;
                        command.ExecuteNonQuery();
                    }
                    else if (soluong != 0)
                    {
                        MessageBox.Show("xuất quá số lượng tờ rời");
                    }
                }
                
                FlxDetail.Rows.Count = 1;
            }
        }
        private int CheckSoLuongThe(int soluong)
        {
            string strMonth;
            int intSonhap = 0, intSoxuat = 0;
            strMonth = Convert.ToString(Convert.ToInt32(ModuleBHXH.GetMonth()) - 1);
            if (strMonth.Substring(4) == "00")
            {
                strMonth = Convert.ToString(Convert.ToInt32(strMonth.Substring(0, 4)) - 1);
                strMonth = strMonth + "12";
            }
            SqlDataReader reader;
            using (SqlConnection connection =
              new SqlConnection(connectionString))
            {
                string queryString;
                queryString = " exec sp_executesql N'SELECT SPACE(6) As Thang, Makho, Mavt, Cuoiky As Dauky, Cuoiky*0 As Nhap, Cuoiky*0 As Xuat, "
                + " Cuoiky, Ma_Quan, SPACE(1) As Loai  FROM SODUVT "
                + " WHERE Thang = @P1  AND Ma_Quan=@P2  AND Makho = @P3  AND Mavt = @P4 ', "
                + " N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(10),@P4 varchar(2)', '" + strMonth + "', '01', '" + ModuleBHXH.strUser + "', 'TT'";

                SqlCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    intSonhap = Convert.ToInt32(reader["Cuoiky"]);
                }
                reader.Close();
                queryString = " exec sp_executesql N'SELECT b.Makho, b.Mavt, a.NX,  SUM(CASE WHEN a.NX=''N'' THEN b.Soluong ELSE 0 END) As Nhap,"
                + " SUM(CASE WHEN a.NX=''X'' THEN b.Soluong ELSE 0 END) As Xuat  FROM CTUVT_HDR a, "
                + " CTUVT_DTL b WHERE a.Keysl=b.Keysl  AND a.Ngayct BETWEEN @P1  AND @P2   AND b.Makho = @P3  "
                + " AND b.Mavt = @P4  GROUP BY b.Makho, b.Mavt, a.NX ', N'@P1 datetime,@P2 datetime,@P3 varchar(10),@P4 varchar(2)', "
                + " '" + ModuleBHXH.strMonths.Substring(4, 2) + "/01/" + ModuleBHXH.strMonths.Substring(0, 4) + "', '" + ModuleBHXH.strDate + "', '" + ModuleBHXH.strUser + "', 'TT'";

                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    intSonhap = intSonhap + Convert.ToInt32(reader["Nhap"]);
                    intSoxuat = intSoxuat + Convert.ToInt32(reader["Xuat"]);
                }
                reader.Close();
                queryString = " exec sp_executesql N'SELECT b.Manv As Makho, b.Mavt, ''N'' As NX,  SUM(b.Soluong) As Nhap  "
                + " FROM CTUVT_HDR a, CTUVT_DTL b WHERE a.Keysl=b.Keysl  AND a.Ngayct BETWEEN @P1  AND @P2   "
                + " AND a.NX=''X'' AND b.Manv<>''''  AND b.Manv = @P3  AND b.Mavt = @P4  GROUP BY b.Manv, b.Mavt ', "
                + " N'@P1 datetime,@P2 datetime,@P3 varchar(10),@P4 varchar(2)', "
                + "'" + ModuleBHXH.strMonths.Substring(4, 2) + "/01/" + ModuleBHXH.strMonths.Substring(0, 4) + "', '" + ModuleBHXH.strDate + "', '" + ModuleBHXH.strUser + "', 'TT'";
                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    intSonhap = intSonhap + Convert.ToInt32(reader["Nhap"]);
                }
                reader.Close();
                queryString = " exec sp_executesql N'SELECT Makho, Mavt, NX,  SUM(CASE WHEN NX=''N'' THEN Soluong ELSE 0 END) As Nhap,"
                + " SUM(CASE WHEN NX=''X'' THEN Soluong ELSE 0 END) As Xuat  FROM CTUVT_SD "
                + " WHERE Ngayct BETWEEN @P1  AND @P2   AND Makho = @P3  AND Mavt = @P4  GROUP BY Makho, Mavt, NX ', "
                + " N'@P1 datetime,@P2 datetime,@P3 varchar(10),@P4 varchar(2)', "
                + "'" + ModuleBHXH.strMonths.Substring(4, 2) + "/01/" + ModuleBHXH.strMonths.Substring(0, 4) + "', '" + ModuleBHXH.strDate + "', '" + ModuleBHXH.strUser + "', 'TT'";
                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    intSonhap = intSonhap + Convert.ToInt32(reader["Nhap"]);
                    intSoxuat = intSoxuat + Convert.ToInt32(reader["Xuat"]);
                }
                reader.Close();
                queryString = " exec sp_executesql N'SELECT Makho, ''TT'' As Mavt, ''X'' As NX,  SUM(Soluong) As Xuat  "
                + " FROM CTUVT_SD WHERE Ngayct BETWEEN @P1  AND @P2  AND NX=''N'' AND (Mavt=''RH'' OR Mavt=''RB'') AND Makho = @P3  GROUP BY Makho ', "
                + " N'@P1 datetime,@P2 datetime,@P3 varchar(10)', "
                + "'" + ModuleBHXH.strMonths.Substring(4, 2) + "/01/" + ModuleBHXH.strMonths.Substring(0, 4) + "', '" + ModuleBHXH.strDate + "', '" + ModuleBHXH.strUser + "'";
                command.CommandText = queryString;
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    intSoxuat = intSoxuat + Convert.ToInt32(reader["Xuat"]);
                }
                reader.Close();
            }
            return intSonhap - intSoxuat;
        }
        #endregion

        

        
       
    }
}
